//
//  FoodCitySql.m
//  FoodFamily
//
//  Created by Bang-guo on 15-6-24.
//  Copyright (c) 2015年 guoBang. All rights reserved.
//

#import "FoodCitySql.h"

@implementation FoodCitySql

+(instancetype)sharDataHandel{

    static FoodCitySql *foodCity = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        foodCity = [[FoodCitySql alloc]init];
        
    });


    return foodCity;


}

static sqlite3 *db = nil;

//打开数据库
-(void)OpenDB{

    if (db != nil) {
        return;
    }
    NSArray *array  =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath = [array lastObject];
    NSString *sqlitePath = [filePath stringByAppendingPathComponent:@"dataBase.sqlite"];
    NSLog(@"***%@",sqlitePath);
    int result = sqlite3_open(sqlitePath.UTF8String, &db);
    if (result ==SQLITE_OK) {
//        NSLog(@"打开数据库成功");
    }else
    {
        
        NSLog(@"打开数据库失败");
    }
    
}


//创建列表
-(void)creatTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS foodCity(number INTEGER PRIMARY KEY AUTOINCREMENT,comment TEXT,user_image TEXT,city_id TEXT,image_height TEXT,want_it_total TEXT,address TEXT,city_name TEXT,picture_id TEXT,place_name TEXT,picture_url TEXT,image_width TEXT,pv_count TEXT,user_name TEXT,food_name TEXT,level_title TEXT,user_id TEXT)";
    
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result ==SQLITE_OK) {
//        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
        
    }
}

//创建附近餐馆的列表

-(void)creatTable1
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS NearbyResturant(number INTEGER PRIMARY KEY AUTOINCREMENT,category TEXT,phone TEXT,place_id TEXT,distance TEXT,address TEXT,category_id TEXT,place_name TEXT,star TEXT)";
    

    
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result ==SQLITE_OK) {
//        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
        
    }
}


//增加元素
-(void)insertFoodCityModel:(FoodCityModle *)foodCM{

    NSString *sql = [NSString stringWithFormat:@"INSERT INTO foodCity(comment,user_image,city_id,image_height,want_it_total,address,city_name,picture_id,place_name,picture_url,image_width,pv_count,user_name,food_name,level_title,user_id)VALUES('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@')",foodCM.comment,foodCM.user_image,foodCM.city_id,foodCM.image_height,foodCM.want_it_total,foodCM.address,foodCM.city_name,foodCM.picture_id,foodCM.place_name,foodCM.picture_url,foodCM.image_width,foodCM.pv_count,foodCM.user_name,foodCM.food_name,foodCM.level_title,foodCM.user_id];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result ==SQLITE_OK) {
//        NSLog(@"添加成功");
    }else
    {
        
        NSLog(@"添加失败");
    }
    

}

//添加元素
-(void)insertNearbyResturantFoodCityModel:(FoodCityModle *)foodCM{

    NSString *sql = [NSString stringWithFormat:@"INSERT INTO NearbyResturant(category,phone,place_id,distance,address,category_id,place_name,star)VALUES('%@','%@','%@','%@','%@','%@','%@','%@')",foodCM.category,foodCM.phone,foodCM.place_id,foodCM.distance,foodCM.address1,foodCM.category_id,foodCM.place_name1,foodCM.star];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result ==SQLITE_OK) {
//        NSLog(@"添加成功");
    }else
    {
        
        NSLog(@"添加失败");
    }



}


-(NSMutableArray *)selectAllFoodCity{
    NSString *sql = @"SELECT * FROM foodCity";
    
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result ==SQLITE_OK) {
//        NSLog(@"准备成功");
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            const unsigned char *comment = sqlite3_column_text(stmt, 1);
            const unsigned char *user_image = sqlite3_column_text(stmt, 2);
            const unsigned char *city_id = sqlite3_column_text(stmt, 3);
            const unsigned char *image_height = sqlite3_column_text(stmt, 4);
            const unsigned char *want_it_total = sqlite3_column_text(stmt, 5);
            const unsigned char *address = sqlite3_column_text(stmt, 6);
            const unsigned char *city_name = sqlite3_column_text(stmt, 7);
            const unsigned char *picture_id = sqlite3_column_text(stmt, 8);
            const unsigned char *place_name = sqlite3_column_text(stmt, 9);
            const unsigned char *picture_url = sqlite3_column_text(stmt, 10);
            const unsigned char *image_width = sqlite3_column_text(stmt, 11);
            const unsigned char *pv_count = sqlite3_column_text(stmt, 12);
            const unsigned char *user_name = sqlite3_column_text(stmt, 13);
            const unsigned char *food_name = sqlite3_column_text(stmt, 14);
            const unsigned char *level_title = sqlite3_column_text(stmt, 15);
            const unsigned char *user_id = sqlite3_column_text(stmt, 16);
            
            FoodCityModle *foodCM = [[FoodCityModle alloc]init];
            foodCM.comment = [NSString stringWithUTF8String:(const char *)comment];
            foodCM.user_image = [NSString stringWithUTF8String:(const char *)user_image];
            foodCM.city_id = [NSString stringWithUTF8String:(const char *)city_id];
            foodCM.image_height = [NSString stringWithUTF8String:(const char *)image_height];
            foodCM.want_it_total = [NSString stringWithUTF8String:(const char *)want_it_total];
            foodCM.address = [NSString stringWithUTF8String:(const char *)address];
            foodCM.city_name = [NSString stringWithUTF8String:(const char *)city_name];
            foodCM.picture_id = [NSString stringWithUTF8String:(const char *)picture_id];
            foodCM.place_name = [NSString stringWithUTF8String:(const char *)place_name];
            foodCM.picture_url = [NSString stringWithUTF8String:(const char *)picture_url];
            foodCM.image_width = [NSString stringWithUTF8String:(const char *)image_width];
            foodCM.pv_count = [NSString stringWithUTF8String:(const char *)pv_count];
            foodCM.user_name = [NSString stringWithUTF8String:(const char *)user_name];
            foodCM.food_name = [NSString stringWithUTF8String:(const char *)food_name];
            foodCM.level_title = [NSString stringWithUTF8String:(const char *)level_title];
            foodCM.user_id = [NSString stringWithUTF8String:(const char *)user_id];
            [array addObject:foodCM];
        }
        
        
        return array;
    }else{
        NSLog(@"准备失败");
        return array;
        
    }

}


-(NSMutableArray *)selectAllNearbyResturant{

    NSString *sql = @"SELECT * FROM NearbyResturant";
    
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result ==SQLITE_OK) {
//        NSLog(@"准备成功");
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            const unsigned char *category = sqlite3_column_text(stmt, 1);
            const unsigned char *phone = sqlite3_column_text(stmt, 2);
            const unsigned char *place_id = sqlite3_column_text(stmt, 3);
            const unsigned char *distance = sqlite3_column_text(stmt, 4);
            const unsigned char *address = sqlite3_column_text(stmt, 5);
            const unsigned char *category_id = sqlite3_column_text(stmt, 6);
            const unsigned char *place_name = sqlite3_column_text(stmt, 7);
            const unsigned char *star = sqlite3_column_text(stmt, 8);
            
            FoodCityModle *foodCM = [[FoodCityModle alloc]init];
            foodCM.category = [NSString stringWithUTF8String:(const char *)category];
            foodCM.phone = [NSString stringWithUTF8String:(const char *)phone];
            foodCM.place_id = [NSString stringWithUTF8String:(const char *)place_id];
            foodCM.distance = [NSString stringWithUTF8String:(const char *)distance];
            foodCM.address1 = [NSString stringWithUTF8String:(const char *)address];
            foodCM.category_id = [NSString stringWithUTF8String:(const char *)category_id];
            foodCM.place_name1 = [NSString stringWithUTF8String:(const char *)place_name];
            foodCM.star = [NSString stringWithUTF8String:(const char *)star];
            [array addObject:foodCM];
        }
        
        
        return array;
    }else{
        NSLog(@"准备失败");
        return array;
        
    }
    

}

-(void)dropTable
{
    NSString *sql = @"DROP TABLE foodCity";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result ==SQLITE_OK) {
//        NSLog(@"删除成功");
    }else
    {
        
        NSLog(@"删除失败");
        
    }
    
    
}




-(void)dropTable1
{
    NSString *sql = @"DROP TABLE NearbyResturant";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result ==SQLITE_OK) {
//        NSLog(@"删除成功");
    }else
    {
        
        NSLog(@"删除失败");
        
    }
    
    
}





@end
